-- 数据库迁移脚本
-- 创建时间：2024
-- 描述：初始化数据库结构，包含USERS和REFRESHTOKENS表

-- 启用外键约束
ALTER SESSION SET CONSTRAINTS = DEFERRED;

-- 删除已存在的表（如果存在）
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE REFRESHTOKENS CASCADE CONSTRAINTS';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE USERS CASCADE CONSTRAINTS';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

-- 创建USERS表
CREATE TABLE USERS (
    USERID NUMBER(10),
    USERNAME NVARCHAR2(50) NOT NULL,
    PASSWORDHASH NVARCHAR2(2000) NOT NULL,
    CREATEDDATE TIMESTAMP(7) DEFAULT SYSTIMESTAMP NOT NULL,
    ISADMIN NUMBER(1) DEFAULT 0 NOT NULL,
    CONSTRAINT PK_USERS PRIMARY KEY (USERID),
    CONSTRAINT UQ_USERS_USERNAME UNIQUE (USERNAME)
);

-- 创建REFRESHTOKENS表
CREATE TABLE REFRESHTOKENS (
    TOKENID NUMBER(10),
    USERID NUMBER(10) NOT NULL,
    TOKEN NVARCHAR2(2000) NOT NULL,
    EXPIRES TIMESTAMP(7) NOT NULL,
    CREATEDDATE TIMESTAMP(7) DEFAULT SYSTIMESTAMP NOT NULL,
    DEVICEINFO NVARCHAR2(1000),
    USECOUNT NUMBER(10) DEFAULT 0 NOT NULL,
    CONSTRAINT PK_REFRESHTOKENS PRIMARY KEY (TOKENID),
    CONSTRAINT FK_REFRESHTOKENS_USERS FOREIGN KEY (USERID)
        REFERENCES USERS (USERID) ON DELETE CASCADE
);

-- 创建索引
CREATE INDEX IDX_USERS_USERNAME ON USERS (USERNAME);
CREATE INDEX IDX_REFRESHTOKENS_TOKEN ON REFRESHTOKENS (TOKEN);
CREATE INDEX IDX_REFRESHTOKENS_USERID ON REFRESHTOKENS (USERID);
CREATE INDEX IDX_REFRESHTOKENS_EXPIRES ON REFRESHTOKENS (EXPIRES);

-- 创建序列（如果需要）
CREATE SEQUENCE USERS_SEQ START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE REFRESHTOKENS_SEQ START WITH 1 INCREMENT BY 1;

-- 创建触发器（用于自增主键，如果需要）
CREATE OR REPLACE TRIGGER USERS_TRG
BEFORE INSERT ON USERS
FOR EACH ROW
BEGIN
    IF :NEW.USERID IS NULL THEN
        SELECT USERS_SEQ.NEXTVAL INTO :NEW.USERID FROM DUAL;
    END IF;
END;
/

CREATE OR REPLACE TRIGGER REFRESHTOKENS_TRG
BEFORE INSERT ON REFRESHTOKENS
FOR EACH ROW
BEGIN
    IF :NEW.TOKENID IS NULL THEN
        SELECT REFRESHTOKENS_SEQ.NEXTVAL INTO :NEW.TOKENID FROM DUAL;
    END IF;
END;
/

-- 插入初始管理员用户（可选，密码需要在应用程序中通过PasswordHasher生成）
-- INSERT INTO USERS (USERNAME, PASSWORDHASH, ISADMIN, CREATEDDATE)
-- VALUES ('admin', 'YOUR_HASHED_PASSWORD_HERE', 1, SYSTIMESTAMP);

-- 创建用于清理过期令牌的存储过程
CREATE OR REPLACE PROCEDURE CLEANEXPIREDREFRESHTOKENS
IS
BEGIN
    DELETE FROM REFRESHTOKENS
    WHERE EXPIRES < SYSTIMESTAMP;
    COMMIT;
END;
/

-- 创建用于清理过期令牌的作业（可选）
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'CLEANUP_EXPIRED_TOKENS',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'CLEANEXPIREDREFRESHTOKENS',
        start_date     => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; INTERVAL=1',
        enabled         => TRUE
    );
END;
/

-- 创建视图：活跃用户视图
CREATE OR REPLACE VIEW ACTIVEUSERS AS
SELECT 
    u.USERID,
    u.USERNAME,
    u.ISADMIN,
    COUNT(r.TOKENID) as ACTIVETOKENCOUNT,
    MAX(r.EXPIRES) as LATESTTOKENEXPIRY
FROM USERS u
LEFT JOIN REFRESHTOKENS r ON u.USERID = r.USERID
WHERE r.EXPIRES > SYSTIMESTAMP OR r.TOKENID IS NULL
GROUP BY u.USERID, u.USERNAME, u.ISADMIN;

-- 授予必要的权限（根据需要调整）
GRANT SELECT, INSERT, UPDATE, DELETE ON USERS TO zlhis;
GRANT SELECT, INSERT, UPDATE, DELETE ON REFRESHTOKENS TO zlhis;
GRANT SELECT ON ACTIVEUSERS TO zlhis;
GRANT EXECUTE ON CLEANEXPIREDREFRESHTOKENS TO zlhis;

-- 提交事务
COMMIT;

-- 添加注释
COMMENT ON TABLE USERS IS '用户表';
COMMENT ON TABLE REFRESHTOKENS IS '刷新令牌表';

-- 验证表创建
SELECT table_name FROM user_tables WHERE table_name IN ('USERS', 'REFRESHTOKENS');
SELECT view_name FROM user_views WHERE view_name = 'ACTIVEUSERS';